﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using HZ.BidSystem.Model;
using HZ.Data;
using System.Data;

namespace HZ.BidSystem.DAL
{
    public class DAL_Bargain
    {
        /// <summary>
        /// 企业议价
        /// </summary>
        /// <param name="bidids">产品编号</param>
        /// <param name="encryptprices">企业输入议价价格</param>
        /// <param name="bidpricesshuoming">价格说明</param>
        /// <returns></returns>
        public static int BargainPrice(IList<int> bidids, IList<string> encryptprices, IList<string> bidpricesshuoming)
        {
            StringBuilder sbsql = new StringBuilder();
            for (int i = 0; i < bidids.Count; i++)
            {
                sbsql.AppendFormat(HZ.Data.DAL_Helper.GetUpdateSql(new Bargain() { BidId = bidids[i], BargainDecPrice = Convert.ToDecimal(encryptprices[i]), BargainEncryptPrice=bidpricesshuoming[i] }));
            }
            int result = HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(sbsql.ToString());
            return result;
        }
        public static int BargainPriceCheck(IList<int> bidids, IList<int> bidcheck)
        {
            StringBuilder sbsql = new StringBuilder();
            for (int i = 0; i < bidids.Count; i++)
            {
                sbsql.AppendFormat(HZ.Data.DAL_Helper.GetUpdateSql(new Bargain() { BidId = bidids[i], BargainEncryptPrice = bidcheck[i].ToString() }));
            }
            int result = HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(sbsql.ToString());
            return result;
        }
        /// <summary>
        /// 解密议价
        /// </summary>
        /// <param name="CompanyId"></param>
        /// <param name="projectid"></param>
        /// <param name="pwd1"></param>
        public static int DecBargain(int? CompanyId, int projectid, string pwd1)
        {
            IDbHelper idbhelper = DbHelperFactory.Create();
            var dt = idbhelper.ExecuteTable(string.Format("SELECT BidId,BargainEncryptPrice FROM dbo.TB_Bargain AS tb WHERE BidId IN (SELECT BidId FROM dbo.TB_Project_Bid WHERE CompanyId = {0} AND ProjectId = {1})", CompanyId.Value, projectid));

            var ie = HZ.Security.EncryptFactory.Instance;
            try
            {
                //idbhelper.BeginTrans();
                foreach (DataRow item in dt.Rows)
                {
                    // 议价不加密
                    if (!string.IsNullOrEmpty(item["BargainEncryptPrice"].ToString()))
                        idbhelper.ExecuteNonQuery(string.Format("UPDATE dbo.TB_Bargain SET BargainDecPrice = {0} WHERE BidId = {1}", item["BargainEncryptPrice"], item["BidId"].ToString()));

                }
                return 1;
                //idbhelper.CommitTrans();
            }
            catch (Exception ee)
            {
                //idbhelper.RollTrans();
                HZ.Common.log4netManager.LogDebug("解密报价", "解密报价发生异常", ee);

                throw ee;
                return 0;
            }
        }

        public static int ClearBargainData()
        {
            try
            {

                int i = HZ.Data.DbHelperFactory.Create().ExecuteNonQuery("DELETE FROM TB_Bargain");
                return 1;
            }
            catch (Exception ee)
            {

                HZ.Common.log4netManager.LogDebug("DAL_Bargain.ClearBargainData", "清空议价数据失败", ee);
                return 0;
            }
        }

        public static int ImportBaragainData(DataTable dt)
        {

            var projectlist = DAL_Project.GetProjectList();
            int projectid = projectlist[projectlist.Count - 1].ProjectId.Value; // 获取项目编号


            Dictionary<string, bool> diccolumns = new Dictionary<string, bool>();
            diccolumns.Add("BidID", false);
            diccolumns.Add("ExpertPrice", false);
            diccolumns.Add("LastPrice", false);

            bool result = true;
            foreach (var item in diccolumns)
            {
                if (dt.Columns.Contains(item.Key))
                {
                }
                else
                {
                    result = false;
                }
            }
            if (!result)
            {
                throw new Exception("字段不符。。请确保存在BidId,ExpertPrice,LastPrice三个字段");
            }

            var idbh = HZ.Data.DbHelperFactory.Create();

            try
            {
                idbh.BeginTrans();
                foreach (DataRow item in dt.Rows)
                {
                    if (item["BidId"].ToString() == "")
                    {
                        continue;
                    }

                    string expertprice = item["ExpertPrice"].ToString();
                    if (expertprice == "")
                    {
                        expertprice = "NULL";
                    }
                    else
                    {
                        if (!HZ.Fun.IsUFloat(expertprice)) // 非数字 直接咔嚓
                        {
                            throw new Exception("专家建议价有非数字");
                        }
                    }

                    string lastprice = item["LastPrice"].ToString();
                    if (lastprice == "")
                    {
                        lastprice = "NULL";
                    }
                    else
                    {
                        if (!HZ.Fun.IsUFloat(lastprice)) // 非数字 直接咔嚓
                        {
                            throw new Exception("上次报价有非数字");
                        }
                    }
                    idbh.ExecuteNonQuery(string.Format("INSERT INTO dbo.TB_Bargain( ProjectId, BidId, ExpertPrice, LastPrice ) VALUES  ({0}, {1}, {2}, {3})", projectid, item["BidId"].ToString(), expertprice, lastprice));
                }
                idbh.CommitTrans();
                return 1;
            }
            catch (Exception eee)
            {
                idbh.RollTrans();
                throw eee;
            }
        }


        /// <summary>
        /// 获取解密情况
        /// </summary>
        /// <param name="where"></param>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public static DataTable GetDecStatus(object where, int currentpage, int pagesize, out int totalrecord)
        {
            throw new NotImplementedException();
        }
        /// <summary>
        /// 获取议价信息
        /// </summary> 
        public static DataTable getBarinPrice(string cg, string CompanyID)
        {
            string sql = string.Format(@"select * from View_BargainPrice1 WHERE 1=1 and BidId='{0}' and companyID='{1}'", cg, CompanyID);

            return HZ.Data.DbHelperFactory.Create().ExecuteTable(sql);
        }

        /// <summary>
        /// 不显示上限价
        /// </summary> 
        public static DataTable getNoBarinPrice(string cg)
        {
            string sql = string.Format(@"select * from TB_NoLimitprice WHERE 1=1 and  Company_Goods='{0}' and Company_Goods in(select Company_Goods from TB_GoodsBargin)", cg);

            return HZ.Data.DbHelperFactory.Create().ExecuteTable(sql);
        }


        public static DataTable GetMinprie(string Company_Goods) 
        {
            string sql = string.Format(@"select Company_Goods,QGBidPrice,QGBidPrice2,HosMinPrice,SCHosAvgPrice,JkMinPrice,DiCengPrice,DiCengXinXi,MinPriceHos,LimitPrice,case when LimitSourceID=1 then '四川现行挂网价' when  LimitSourceID=2 then '外省最低五省均价' when LimitSourceID=3 then '全国二级及以上公立医院最低三次均价' when LimitSourceID=4 then '相似产品上限价换算值'  else '' end LimitSource 
                from TB_GoodsBargin WHERE 1=1 and Company_Goods='{0}'", Company_Goods);//select * from TB_MinPrice 
            return HZ.Data.DbHelperFactory.Create().ExecuteTable(sql); 
        }
        /// <summary>
        /// 企业确认议价数据是否进入议价流程
        /// </summary>
        /// <param name="objquery"></param>
        /// <param name="currentPage"></param>
        /// <param name="pageSize"></param>
        /// <param name="totalRecord"></param>
        /// <returns></returns>
        public static DataTable GetGoodsBarin(object objquery, int currentPage, int pageSize, out int totalRecord)
        {
            var where = HZ.Data.DAL_Helper.GetWhereCondition(objquery, true);
            string strSql = string.Format(@"select * from TB_GoodsBargin  WHERE 1=1 {0} ", where);
            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(strSql, pageSize);
            des.CurrentPage = currentPage;
            totalRecord = des.RecordCount;
            return des.GetSpecailPage();
        }
        /// <summary>
        /// 企业：获取专家第一轮指导价
        /// </summary> 
        public static DataTable GetBarinBidPrice(object objquery, int currentPage, int pageSize, out int totalRecord)
        {
            var where = HZ.Data.DAL_Helper.GetWhereCondition(objquery, true);
            string strSql = string.Format(@"SELECT * from View_BargainPrice1  WHERE 1=1 {0} ", where);
            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(strSql, pageSize);
            des.CurrentPage = currentPage;
            totalRecord = des.RecordCount;
            return des.GetSpecailPage();

        }
        /// <summary>
        /// 企业：获取专家第二轮指导价
        /// </summary> 
        public static DataTable GetBarinBidPrice2(object objquery, int currentPage, int pageSize, out int totalRecord)
        {
            var where = HZ.Data.DAL_Helper.GetWhereCondition(objquery, true);
            string strSql = string.Format(@"SELECT *from View_BargainPrice2  WHERE 1=1 {0} ", where);
            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(strSql, pageSize);
            des.CurrentPage = currentPage;
            totalRecord = des.RecordCount;
            return des.GetSpecailPage();

        }


        /// <summary>
        /// 第一次确认议价价格
        /// </summary>
        /// <param name="barin"></param>
        /// <returns></returns>
        public static int UpdateBarinPrice(HZ.BidSystem.Model.View.View_BargainPrice2 barin)
        {
            try
            {
                var idb = HZ.Data.DbHelperFactory.Create();
                string strSQL = "";
                if (barin.Switch == 1)
                {
                    strSQL = string.Format(@"update TB_Bargain set Switch='{2}',AcceptDate=getDate(),BargainDecPrice=null,BargainEncryptPrice=null where BidId='{0}' and companyID={1}", barin.BidId, barin.CompanyId_Tb, barin.Switch);
                }
                else
                {
                    strSQL = string.Format(@"update TB_Bargain set Switch='{2}',AcceptDate=getDate(),BargainDecPrice='{3}',BargainEncryptPrice='{4}' where BidId='{0}' and companyID={1}", barin.BidId, barin.CompanyId_Tb, barin.Switch, barin.BargainDecPrice, barin.BargainEncryptPrice);
                }
                idb.ExecuteNonQuery(strSQL);
                return 1;
            }
            catch (Exception ex)
            {

                return 0;
                throw ex;
            }

        }

        /// <summary>
        /// 是否确认议价产品进行议价
        /// </summary>
        /// <param name="barin"></param>
        /// <returns></returns>
        public static int UpdateGoodsBarin(HZ.BidSystem.Model.View.View_BargainPrice2 barin)
        {
            try
            {
                var idb = HZ.Data.DbHelperFactory.Create();
                string strSQL = string.Format(@"update TB_GoodsBargin set CheckStatus='{1}',AcceptDate=getDate() where Company_Goods='{0}'", barin.Company_Goods, barin.CheckStatus);
                idb.ExecuteNonQuery(strSQL);
                return 1;
            }
            catch (Exception ex)
            {
                return 0;
                throw ex;
            }
        }


        /// <summary>
        /// 第二次确认议价价格---企业端
        /// </summary>
        /// <param name="barin"></param>
        /// <returns></returns>
        public static int UpdateBarinPrice2(HZ.BidSystem.Model.View.View_BargainPrice2 barin)
        {
            try
            {
                var idb = HZ.Data.DbHelperFactory.Create();
                string strSQL = "";
                if (barin.Switch == 1)
                {
                    strSQL = string.Format(@"update TB_Bargain2 set Switch=1,AcceptDate=getDate() where BidId='{0}' and companyID={1}", barin.BidId, barin.CompanyId_Tb);
                }
                else
                {
                    strSQL = string.Format(@"update TB_Bargain2 set Switch=2,AcceptDate=getDate() where BidId='{0}' and companyID={1}", barin.BidId, barin.CompanyId_Tb);
                }
                idb.ExecuteNonQuery(strSQL);
                return 1;
            }
            catch (Exception ex)
            {

                return 0;
                throw ex;
            }

        }

        /// <summary>
        /// 第二次确认议价价格
        /// </summary>
        /// <param name="barin"></param>
        /// <returns></returns>
        public static int UpdateBarinPrice2Argee(int ExpertState,string Company_Goods,string UserID)
        {
            try
            {
                var idb = HZ.Data.DbHelperFactory.Create();
                string strSQL = "";
                if (ExpertState == 1)
                {
                    string SQLa = string.Format(@"select BargainDecPrice from TB_Bargain where BidId='{0}' ", Company_Goods);
                    string SQLb = string.Format(@"select LimitPrice from TB_GoodsBargin where Company_Goods='{0}' ", Company_Goods);
                    decimal flag = Convert.ToDecimal(HZ.Data.DbHelperFactory.Create().ExecuteScalar(SQLa));
                    decimal flag1 = Convert.ToDecimal(HZ.Data.DbHelperFactory.Create().ExecuteScalar(SQLb));
                    if (flag > flag1)
                    {
                        return 3;
                    }
                    else
                    {
                        strSQL = string.Format(@"update PB_BargainExpertPrice set ExpertState=1,ExpertPrice2=B.BargainDecPrice from dbo.PB_BargainExpertPrice A INNER JOIN dbo.TB_Bargain AS b ON A.Company_Goods = b.BidId where Company_Goods='{0}' and UserID='{1}'", Company_Goods, UserID);
                    }
                }
                else
                {
                    strSQL = string.Format(@"update PB_BargainExpertPrice set ExpertState=2 where Company_Goods='{0}' and UserID='{1}'", Company_Goods, UserID);
                }
                idb.ExecuteNonQuery(strSQL);
                return 1;
            }
            catch (Exception ex)
            {

                return 0;
                throw ex;
            }

        }

        /// <summary>
        /// 更新产品专家建议价
        /// </summary>
        /// <param name="PriceTable">价格信息</param>
        /// <param name="ExpertPriceNum"> 轮次</param>
        /// <returns></returns>
        public static int UpdateBargainPriceBatch(System.Data.DataTable PriceTable, int ExpertPriceNum)
        {
            int Flag = 0;
            HZ.Data.IDbHelper idbh = HZ.Data.DbHelperFactory.Create();
            try
            {
                StringBuilder sbSQL = new StringBuilder();
                idbh.BeginTrans();
                for (int i = 0; i < PriceTable.Rows.Count; i++)
                {
                    if (ExpertPriceNum == 1)
                    {
                        sbSQL.AppendFormat(@"UPDATE  [PB_BargainExpertPrice]
                                                   SET  
                                                       [ExpertPrice1] = {0}
                                                      ,[ExpertPriceTime1] = getdate()
                                                      ,[ExpertPriceFlag1] = 1
                                                 WHERE [Company_Goods] = '{1}' AND  [UserID] ='{2}'; ", PriceTable.Rows[i]["Price"].ToString(), PriceTable.Rows[i]["Company_Goods"].ToString(), PriceTable.Rows[i]["UserID"].ToString());
                    }
                    if (ExpertPriceNum == 2)
                    {
                        sbSQL.AppendFormat(@"UPDATE  [PB_BargainExpertPrice]
                                                   SET  
                                                       [ExpertPrice2] = {0}
                                                      ,[ExpertPriceTime2] = getdate()
                                                      ,[ExpertPriceFlag2] = 1
                                                 WHERE [Company_Goods] = '{1}' AND  [UserID] ='{2}'; ", PriceTable.Rows[i]["Price"].ToString(), PriceTable.Rows[i]["Company_Goods"].ToString(), PriceTable.Rows[i]["UserID"].ToString());
                    }

                }
                idbh.ExecuteNonQuery(sbSQL.ToString());
                idbh.CommitTrans();
                Flag = 1;
            }
            catch (Exception ex)
            {
                idbh.RollTrans();
                Flag = 0;
            }
            return Flag;
        }

        /// <summary>
        /// 获取专家给价格数据
        /// </summary>
        /// <param name="objquery"></param>
        /// <param name="currentPage"></param>
        /// <param name="pageSize"></param>
        /// <param name="totalRecord"></param>
        /// <returns></returns>
        public static DataTable GetExpertPrice2(object objquery, int currentPage, int pageSize, out int totalRecord)
        {
            var where = HZ.Data.DAL_Helper.GetWhereCondition(objquery, true);
            string strSql = string.Format(@"SELECT g.Company_Goods,g.ProductName,g.GoodsName,g.Model,g.ModelSmall,g.Outlookc,g.MaterialUnit,g.PackFactor,g.PackUnit, 
		g.PackMaterials,g.Weights,g.CompanyName_SC,g.CompanyName_Tb,g.CompanyId_Tb,g.CompanyCode_Tb,g.LimitPrice,pb.ExpertState,g.QualityLevelName,g.SortID,
			CASE WHEN ISNULL(pb.ExpertPriceFlag2,0)=1 THEN '已给价' ELSE '未给价' END ExpertPriceFlag2,g.LimitSourceID,g.LimitSource,g.ComGoodsLike, 
			CASE WHEN ISNULL(pb.ExpertPriceSubmitStatus2,0)=1 THEN '已提交' ELSE '未提交' END ExpertPriceSubmitStatus2 ,
         pb.UserId, ExpertGroupID, ExpertPriceNum, ExpertPrice1 专家第1轮建议价, ExpertPriceTime1, ExpertPriceFlag1, ExpertPriceSubmitStatus1,
		 ExpertPriceSubmitTime1,pb.ExpertPrice2 专家第2轮建议价, B.ExpertPrice 专家组第1轮建议价, ExpertPriceTime2, ExpertPriceSubmitTime2,
		 B.BargainDecPrice 企业第1轮还价,B.BargainEncryptPrice 企业第1轮还价理由 
        FROM  dbo.TB_GoodsBargin AS g INNER JOIN dbo.PB_BargainExpertPrice  AS pb ON g.Company_Goods = pb.Company_Goods 
		        INNER JOIN dbo.TB_Bargain AS b ON g.Company_Goods = b.BidId  WHERE 1=1 {0} ", where);
            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(strSql, pageSize);
            des.CurrentPage = currentPage;
            totalRecord = des.RecordCount;
            return des.GetSpecailPage();

        }

        /// <summary>
        /// 获取专家给价格数据
        /// </summary> 
        public static DataTable GetExpertPrice(object objquery, int currentPage, int pageSize, out int totalRecord)
        {
            var where = HZ.Data.DAL_Helper.GetWhereCondition(objquery, true);
            string strSql = string.Format(@"SELECT  g.Company_Goods,g.ProductName,g.GoodsName,g.Model,g.ModelSmall,g.Outlookc,g.MaterialUnit,g.PackFactor,g.PackUnit, 
		g.PackMaterials,g.Weights,g.CompanyName_SC,g.CompanyName_Tb,g.CompanyId_Tb,g.CompanyCode_Tb,g.LimitPrice, 
		CASE WHEN ISNULL(pb.ExpertPriceFlag1,0)=1 THEN '已给价' ELSE '未给价' END ExpertPriceFlag1 ,
		CASE WHEN ISNULL(pb.ExpertPriceSubmitStatus1,0)=1 THEN '已提交' ELSE '未提交' END ExpertPriceSubmitStatus1 ,
        pb.UserId, ExpertGroupID, ExpertPriceNum, ExpertPrice1, ExpertPriceTime1, ExpertPriceSubmitTime1, ExpertPrice2,
		ExpertPriceTime2, ExpertPriceFlag2, ExpertPriceSubmitStatus2, ExpertPriceSubmitTime2,g.LimitSourceID,g.LimitSource,g.ComGoodsLike,g.SortID,g.QualityLevelName   
        FROM dbo.TB_GoodsBargin AS g INNER JOIN dbo.PB_BargainExpertPrice AS pb ON g.Company_Goods = pb.Company_Goods WHERE 1=1 {0} ", where);
            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(strSql, pageSize);
            des.CurrentPage = currentPage;
            totalRecord = des.RecordCount;
            return des.GetSpecailPage();
        }



        /// <summary>
        /// 获取专家给价格数据---仅供导出
        /// </summary> 
        public static DataTable GetExpertPriceZJ2(object objquery, int currentPage, int pageSize, out int totalRecord)
        {
            var where = HZ.Data.DAL_Helper.GetWhereCondition(objquery, true);
            string strSql = string.Format(@"SELECT  g.Company_Goods 产品编号,g.ProductName 通用名,g.GoodsName 商品名,g.Model 剂型,g.ModelSmall 细化剂型,g.Outlookc 规格,g.MaterialUnit 最小制剂单位,g.PackFactor 转换比,g.PackUnit 单位, 
		g.Weights 装重量,g.CompanyName_SC 生产企业,g.CompanyName_Tb 投标企业,g.LimitPrice 上限价, 
		CASE WHEN ISNULL(pb.ExpertPriceFlag1,0)=1 THEN '已给价' ELSE '未给价' END ExpertPriceFlag1 ,
		CASE WHEN ISNULL(pb.ExpertPriceSubmitStatus1,0)=1 THEN '已提交' ELSE '未提交' END ExpertPriceSubmitStatus1 ,
        pb.UserId 用户编号, ExpertPrice1 第一轮建议价, ExpertPrice2 第二轮建议价   
        FROM dbo.TB_GoodsBargin AS g INNER JOIN dbo.PB_BargainExpertPrice AS pb ON g.Company_Goods = pb.Company_Goods WHERE 1=1 {0} ", where);
            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(strSql, pageSize);
            des.CurrentPage = currentPage;
            totalRecord = des.RecordCount;
            return des.GetSpecailPage();
        }


        /// <summary>
        /// 获取专家给价格数据导出
        /// </summary> 
        public static DataTable GetExpertPriceZJ(object objquery, int currentPage, int pageSize, out int totalRecord)
        {
            var where = HZ.Data.DAL_Helper.GetWhereCondition(objquery, true);
            string strSql = string.Format(@"SELECT  g.Company_Goods 产品编号,g.ProductName 通用名,g.GoodsName 商品名,g.Model 剂型,g.ModelSmall 细化剂型,g.Outlookc 规格,g.MaterialUnit 最小制剂单位,g.PackFactor 转换比,g.PackUnit 单位, 
		g.Weights 装重量,g.CompanyName_SC 生产企业,g.CompanyName_Tb 投标企业,g.LimitPrice 上限价, 
		CASE WHEN ISNULL(pb.ExpertPriceFlag1,0)=1 THEN '已给价' ELSE '未给价' END 给价状态 ,
		CASE WHEN ISNULL(pb.ExpertPriceSubmitStatus1,0)=1 THEN '已提交' ELSE '未提交' END 提交状态 ,
        pb.UserId 用户编号, ExpertPrice1 第一轮建议价   
        FROM dbo.TB_GoodsBargin AS g INNER JOIN dbo.PB_BargainExpertPrice AS pb ON g.Company_Goods = pb.Company_Goods WHERE 1=1 {0} ", where);
            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(strSql, pageSize);
            des.CurrentPage = currentPage;
            totalRecord = des.RecordCount;
            return des.GetSpecailPage();
        }


        //同招标单元拟中标价 
        public static DataTable GetSortGoodsPrice(string sortid, int currentPage, int pageSize, out int totalRecord)
        {
            string strSql = string.Format(@"select * from TB_SortGoodsPrice where 招标单元='{0}' order by 产品编号", sortid);
            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(strSql, pageSize);
            des.CurrentPage = currentPage;
            totalRecord = des.RecordCount;
            return des.GetSpecailPage();
        }
        //相似产品上限价换算
        public static DataTable GetLikecg(string cg)
        {
            string sql = string.Format("select * from TB_LikeGoods where 产品编号='{0}'",cg);
            return HZ.Data.DbHelperFactory.Create().ExecuteTable(sql);
        }
        /// <summary>
        /// 中心查看专家议价情况
        /// </summary> 
        public static DataTable GetBarginListPrice(string objquery, int currentPage, int pageSize, out int totalRecord)
        {
            string strsql = string.Format(@"select a.UserId,AccountName,d.UserName, isnull(产品总数,0) 产品总数,isnull(已给价产品数,0) 已给价产品数,isnull(未给价产品数,0) 未给价产品数,case when IsSubmit>0 then '已提交' else '未提交' end IsSubmit from 
        (select COUNT(distinct Company_Goods)  产品总数,UserID from PB_BARGAINEXPERTPRICE where   [ExpertPriceNum]=1 group by UserID )a 
        left join 
        (select COUNT(distinct Company_Goods)  已给价产品数,UserID from PB_BARGAINEXPERTPRICE where  [ExpertPriceNum]=1 AND ISNULL([ExpertPriceFlag1],0)=1  group by UserID )b on a.UserID=b.UserID
        left join 
        (select COUNT(distinct Company_Goods) 未给价产品数,UserID from dbo.PB_BARGAINEXPERTPRICE where  [ExpertPriceNum]=1 AND ISNULL([ExpertPriceFlag1],0)=0  group by UserID  )c on a.UserID=c.UserID
		left join 
		(select sum(ISNULL(ExpertPriceSubmitStatus1,0)) IsSubmit,UserID from dbo.PB_BARGAINEXPERTPRICE where  [ExpertPriceNum]=1 AND ISNULL([ExpertPriceFlag1],0)=1  group by UserID) e on a.UserID=e.UserID
		left join TB_Account d on A.UserId=d.AccountName where 1=1 {0} Order by AccountName", objquery);
            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(strsql, pageSize);
            des.CurrentPage = currentPage;
            totalRecord = des.RecordCount;
            return des.GetSpecailPage();

        }


        /// <summary>
        /// 中心查看专家议价情况  第二轮
        /// </summary> 
        public static DataTable GetBarginListPrice2(string objquery, int currentPage, int pageSize, out int totalRecord)
        {
            string strsql = string.Format(@"select a.UserId,AccountName,d.UserName, isnull(产品总数,0) 产品总数,isnull(已给价产品数,0) 已给价产品数,isnull(未给价产品数,0) 未给价产品数,case when IsSubmit>0 then '已提交' else '未提交' end IsSubmit  from 
        (select COUNT(distinct Company_Goods)  产品总数,UserID from PB_BARGAINEXPERTPRICE where   [ExpertPriceNum]=2 group by UserID )a 
        left join 
        (select COUNT(distinct Company_Goods)  已给价产品数,UserID from PB_BARGAINEXPERTPRICE where  [ExpertPriceNum]=2 AND ISNULL([ExpertPriceFlag2],0)=1  group by UserID )b on a.UserID=b.UserID
        left join 
        (select COUNT(distinct Company_Goods) 未给价产品数,UserID from dbo.PB_BARGAINEXPERTPRICE where  [ExpertPriceNum]=2 AND ISNULL([ExpertPriceFlag2],0)=0  group by UserID  )c on a.UserID=c.UserID
	    left join 
		(select sum(ISNULL(ExpertPriceSubmitStatus2,0)) IsSubmit,UserID from dbo.PB_BARGAINEXPERTPRICE where  [ExpertPriceNum]=2 AND ISNULL([ExpertPriceFlag2],0)=1  group by UserID) e on a.UserID=e.UserID
		left join TB_Account d on A.UserId=d.AccountName where 1=1 {0} Order by AccountName", objquery);
            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(strsql, pageSize);
            des.CurrentPage = currentPage;
            totalRecord = des.RecordCount;
            return des.GetSpecailPage();

        }

        /// <summary>
        /// 专家给价进场查看
        /// </summary>
        /// <param name="objquery"></param>
        /// <param name="currentPage"></param>
        /// <param name="pageSize"></param>
        /// <param name="totalRecord"></param>
        /// <returns></returns>
        public static DataTable GetExpertPriceIng(object objquery, int currentPage, int pageSize, out int totalRecord)
        {
            var where = HZ.Data.DAL_Helper.GetWhereCondition(objquery, true);
            string strSql = string.Format(@" select * from [dbo].[View_BargainExpertPriceIng]  WHERE 1=1 {0} ", where);
            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(strSql, pageSize);
            des.CurrentPage = currentPage;
            totalRecord = des.RecordCount;
            return des.GetSpecailPage();

        }



        /// <summary>
        /// 最终议价状态
        /// </summary>
        /// <param name="objquery"></param>
        /// <param name="currentPage"></param>
        /// <param name="pageSize"></param>
        /// <param name="totalRecord"></param>
        /// <returns></returns>
        public static DataTable GetExpertPriceEnd(object objquery, int currentPage, int pageSize, out int totalRecord)
        {
            var where = HZ.Data.DAL_Helper.GetWhereCondition(objquery, true);
            string strSql = string.Format(@"	    
SELECT  g.Company_Goods, g.ProductName,g.GoodsName,g.Model,g.ModelSmall, g.Outlookc,g.MaterialUnit,g.PackFactor,  g.PackUnit, 
		    g.PackMaterials,g.Weights,g.CompanyName_SC,G.LimitPrice, g.CompanyName_Tb, g.CompanyId_Tb,g.CompanyCode_Tb
				,pb.ExpertPrice 专家组第一轮建议价,pb.BargainDecPrice 企业还价,
				CASE WHEN isnull(pb.ExpertPrice,0)=0 then '专家未给价' when ISNULL(pb.Switch ,0)=1 THEN '企业同意' when ISNULL(pb.Switch ,0)=0 THEN '企业未响应' ELSE '企业已还价' END 一轮状态 ,
			pb.BargainEncryptPrice 还价理由,CASE WHEN ISNULL(pb.Switch ,0)=1 THEN '拟中标' when ISNULL(pb.Switch ,0)=0 THEN '未操作' ELSE '进入二轮议价
' END 最终状态
            FROM dbo.TB_GoodsBargin AS g inner JOIN
             (Select * from  TB_Bargain) AS pb ON g.Company_Goods = pb.BidId   WHERE 1=1 {0} ", where);
            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(strSql, pageSize);
            des.CurrentPage = currentPage;
            totalRecord = des.RecordCount;
            return des.GetSpecailPage();

        }

        /// <summary>
        /// 最终议价状态------导出用
        /// </summary>
        /// <param name="objquery"></param>
        /// <param name="currentPage"></param>
        /// <param name="pageSize"></param>
        /// <param name="totalRecord"></param>
        /// <returns></returns>
        public static DataTable GetExpertPriceEndExport(object objquery, int currentPage, int pageSize, out int totalRecord)
        {
            var where = HZ.Data.DAL_Helper.GetWhereCondition(objquery, true);
            string strSql = string.Format(@"	    
SELECT  g.Company_Goods 编号, g.ProductName 通用名,g.GoodsName 商品名,g.Model 剂型,g.ModelSmall, g.Outlookc 规格,g.MaterialUnit,g.PackFactor 转换比,  g.PackUnit 包装单位, 
		    g.PackMaterials 包装,g.Weights 装重量,g.CompanyName_SC 生产企业,G.LimitPrice 限价, g.CompanyName_Tb 投标企业, g.CompanyId_Tb,g.CompanyCode_Tb 投标企业编号
				,pb.ExpertPrice 专家组第一轮建议价,pb.BargainDecPrice 企业还价,
				CASE WHEN isnull(pb.ExpertPrice,0)=0 then '专家未给价' when ISNULL(pb.Switch ,0)=1 THEN '企业同意' when ISNULL(pb.Switch ,0)=0 THEN '企业未响应' ELSE '企业已还价' END 一轮状态 ,
			pb.BargainEncryptPrice 还价理由,CASE WHEN ISNULL(pb.Switch ,0)=1 THEN '拟中标' when ISNULL(pb.Switch ,0)=0 THEN '未操作' ELSE '进入二轮议价
' END 最终状态
            FROM dbo.TB_GoodsBargin AS g inner JOIN
             (Select * from  TB_Bargain) AS pb ON g.Company_Goods = pb.BidId   WHERE 1=1 {0} ", where);
            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(strSql, pageSize);
            des.CurrentPage = currentPage;
            totalRecord = des.RecordCount;
            return des.GetSpecailPage();

        }


        /// <summary>
        /// 最终议价状态
        /// </summary>
        /// <param name="objquery"></param>
        /// <param name="currentPage"></param>
        /// <param name="pageSize"></param>
        /// <param name="totalRecord"></param>
        /// <returns></returns>
        public static DataTable GetExpertPriceEnd2(object objquery, int currentPage, int pageSize, out int totalRecord)
        {
            var where = HZ.Data.DAL_Helper.GetWhereCondition(objquery, true);
            string strSql = string.Format(@"	    
SELECT  g.Company_Goods, g.ProductName,g.GoodsName,g.Model,g.ModelSmall, g.Outlookc,g.MaterialUnit,g.PackFactor,  g.PackUnit, 
		    g.PackMaterials,g.Weights,g.CompanyName_SC,G.LimitPrice, g.CompanyName_Tb, g.CompanyId_Tb,g.CompanyCode_Tb
				,pd.ExpertPrice 专家组第一轮建议价,case when ExpertStateNum between 0 and 7 then ba.ExpertPrice2 else ba.ExpertPrice2 end 专家组第二轮建议价 ,
			CASE when ISNULL(ba.Switch ,0)=0   THEN '企业未响应' when ISNULL(ba.Switch ,0)=1 THEN '企业同意' 
			when ISNULL(ba.ExpertPrice2 ,0)=0  THEN '专家未给价'  ELSE '企业拒绝'
            END 二轮状态, 
			CASE when ISNULL(ba.Switch ,0)=0   THEN '未操作' when ISNULL(ba.Switch ,0)=1 THEN '拟中标' when ISNULL(ba.Switch ,0)=2 THEN '弃标'  ELSE '弃标'
            END 最终状态
            FROM dbo.TB_GoodsBargin AS g inner JOIN
			(select Company_Goods,sum(ExpertState) ExpertStateNum from PB_BargainExpertPrice where ExpertPriceNum=2 group by Company_Goods) ex
			 on g.Company_Goods=ex.Company_Goods
			 inner join 
			(Select * from  TB_Bargain) AS pd on g.Company_Goods=pd.BidId left join  
			(Select * from  TB_Bargain2) AS ba ON g.Company_Goods = ba.BidId   WHERE 1=1 {0} ", where);
            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(strSql, pageSize);
            des.CurrentPage = currentPage;
            totalRecord = des.RecordCount;
            return des.GetSpecailPage();

        }


        /// <summary>
        /// 最终议价状态---导出用
        /// </summary>
        /// <param name="objquery"></param>
        /// <param name="currentPage"></param>
        /// <param name="pageSize"></param>
        /// <param name="totalRecord"></param>
        /// <returns></returns>
        public static DataTable GetExpertPriceEndExport2(object objquery, int currentPage, int pageSize, out int totalRecord)
        {
            var where = HZ.Data.DAL_Helper.GetWhereCondition(objquery, true);
            string strSql = string.Format(@"	    
SELECT  g.Company_Goods 编号, g.ProductName 通用名,g.GoodsName 商品名,g.Model 剂型,g.ModelSmall, g.Outlookc 规格,g.MaterialUnit 单位,g.PackFactor 转换比,  g.PackUnit 包装单位, 
		    g.PackMaterials 包装,g.Weights 装重量,g.CompanyName_SC 生产企业,G.LimitPrice 限价, g.CompanyName_Tb 投标企业, g.CompanyId_Tb,g.CompanyCode_Tb 投标企业编号
				,pd.ExpertPrice 专家组第一轮建议价,ba.ExpertPrice2 专家组第二轮建议价,
				CASE WHEN ISNULL(ba.Switch ,0)=1 THEN '企业同意'
				when ISNULL(ba.ExpertPrice2 ,0)=0   THEN '专家未给价' when ISNULL(ba.Switch ,0)=0   THEN '企业未响应'    ELSE '企业拒绝'
            END 二轮状态, 
			CASE WHEN ISNULL(ba.Switch ,0)=1 THEN '拟中标' when ISNULL(ba.Switch ,0)=0   THEN '未操作'    ELSE '弃标'
            END 最终状态
            FROM dbo.TB_GoodsBargin AS g inner JOIN
			(Select * from  TB_Bargain) AS pd on g.Company_Goods=pd.BidId inner join  
			   (Select * from  TB_Bargain2) AS ba ON g.Company_Goods = ba.BidId   WHERE 1=1 {0} ", where);
            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(strSql, pageSize);
            des.CurrentPage = currentPage;
            totalRecord = des.RecordCount;
            return des.GetSpecailPage();

        }

        /// <summary>
        /// 专家确认提交建议价
        /// </summary>
        /// <param name="ExpertID">专家号</param>
        /// <param name="ExpertPriceNum"> 轮次</param>
        /// <returns>1.成功，0 失败，-1有未打完价格的</returns>
        public static int ExpertPriceSubmit(string ExpertID, int ExpertPriceNum)
        {
            int Flag = 0;
            HZ.Data.IDbHelper idbh = HZ.Data.DbHelperFactory.Create();
            DataTable dt = new DataTable();

            if (ExpertPriceNum == 1)
            {
                int i = (int)idbh.ExecuteScalar(string.Format("select count(1) from PB_BARGAINEXPERTPRICE where [UserID]='{0}' AND [ExpertPriceNum]={1} and ExpertPriceSubmitStatus1=1 ", ExpertID, ExpertPriceNum));
                if (i > 0)
                {
                    return 3;//代表已提交
                }
                dt = idbh.ExecuteTable(string.Format(@"SELECT COUNT(1) num FROM PB_BARGAINEXPERTPRICE
WHERE  [UserID]='{0}' AND [ExpertPriceNum]={1} AND ISNULL([ExpertPriceFlag1],0)=0", ExpertID, ExpertPriceNum.ToString()));
            }
            if (ExpertPriceNum == 2)
            {
                int i = (int)idbh.ExecuteScalar(string.Format("select count(1) from PB_BARGAINEXPERTPRICE where [UserID]='{0}' AND [ExpertPriceNum]={1} and ExpertPriceSubmitStatus2=1 ", ExpertID, ExpertPriceNum));
                if (i > 0)
                {
                    return 3;//代表已提交
                }
                dt = idbh.ExecuteTable(string.Format(@"SELECT COUNT(1) num FROM PB_BARGAINEXPERTPRICE
WHERE  [UserID]='{0}' AND [ExpertPriceNum]={1} AND ISNULL([ExpertPriceFlag2],0)=0", ExpertID, ExpertPriceNum.ToString()));
            }

            if (int.Parse(dt.Rows[0][0].ToString()) > 0)
            {
                Flag = -1;//说明有未打完价格的
            }
            else
            {
                try
                {
                    StringBuilder sbSQL = new StringBuilder();
                    idbh.BeginTrans();

                    if (ExpertPriceNum == 1)
                    {
                        sbSQL.AppendFormat(@"UPDATE  [PB_BargainExpertPrice]
                                                   SET  
                                                       [ExpertPriceSubmitStatus1] = 1
                                                      ,[ExpertPriceSubmitTime1] = getdate()
                                                 WHERE [UserID] ='{0}' and ExpertPriceNum=1; ", ExpertID);
                    }
                    if (ExpertPriceNum == 2)
                    {
                        sbSQL.AppendFormat(@"UPDATE  [PB_BargainExpertPrice]
                                                  SET  
                                                       [ExpertPriceSubmitStatus2] = 1
                                                      ,[ExpertPriceSubmitTime2] = getdate()
                                                 WHERE [UserID] ='{0}' and ExpertPriceNum=2; ", ExpertID);
                    }

                    idbh.ExecuteNonQuery(sbSQL.ToString());
                    idbh.CommitTrans();
                    Flag = 1;
                }
                catch (Exception)
                {
                    idbh.RollTrans();
                    Flag = 0;
                }
            }

            return Flag;
        }

    }
}
